﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using System.Linq;
using System.Text;
using JKF.BusinessLayer.BLL;
using JKF.DataModule;
using JKF.DataModule.SqlServerDAL;
using JKF.Entities.Model;
using JKF.Substructure;

namespace JKF.BusinessLayer.BLL
{
    public partial class bllPOINT_EXCHANGE
    {
        /// <summary>
        /// 取得兑换记录列表
        /// </summary>
        /// <param name="searchItem"></param>
        /// <param name="matchWay"></param>
        /// <param name="keyword"></param>
        /// <param name="dateStart"></param>
        /// <param name="dateEnd"></param>
        /// <param name="pageCurrent"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageCount"></param>
        /// <param name="rowCount"></param>
        /// <returns></returns>
         public DataTable GetPointExchange(string searchItem, string matchWay, string keyword, string dateStart, string dateEnd,
           string ownShop, int pageCurrent, int pageSize, ref int pageCount, ref int rowCount)
         {
             dalPOINT_EXCHANGE dalPointExchange = new dalPOINT_EXCHANGE();
             string userKey = bllSYS_USER.CurrentUser.USER_KEY.ToString();
             return dalPointExchange.GetPointExchange(searchItem, matchWay, keyword, dateStart, dateEnd, ownShop, userKey, pageCurrent, pageSize, ref pageCount, ref rowCount);
         }

         public class ExchangeItemJson
         {
             public string GIFT_KEY;
             public string GIFT_NAME;
             public string NUM;
             public string DEDUCT_POINT;
         }

        /// <summary>
        /// 兑换礼品时选择顾客
        /// </summary>
        /// <param name="keyword"></param>
        /// <returns></returns>
         public DataRow GetCustomer(string keyword)
         {
             if (string.IsNullOrEmpty(keyword))
                 return null;

             var strSql = @"SELECT A.CUSTOMER_KEY,A.CARD_NO,A.NAME,A.MOBILE,B.NAME AS LEVEL_NAME,GIVE_POINTS AS POINTS
             FROM dbo.CUSTOMER A
             LEFT JOIN dbo.DIC_LEVEL B ON A.LEVEL_CODE=B.LEVEL_CODE
             INNER JOIN dbo.SYS_USER_SHOP  C ON A.SHOP_CODE=C.SHOP_CODE
             WHERE (A.CARD_NO=@keyword or A.MOBILE=@keyword) AND  C.USER_KEY=@USER_KEY";
             var dbClass = DB.HR;
             var cmd = dbClass.GetSqlStringCommand(strSql);
             dbClass.AddInParameter(cmd, "keyword", DbType.String, keyword);
             dbClass.AddInParameter(cmd, "user_key", DbType.Guid, bllSYS_USER.CurrentUser.USER_KEY);
             var dt = dbClass.ExecuteDataSet(cmd).Tables[0];
             return (dt != null && dt.Rows.Count > 0) ? dt.Rows[0] : null;
         }

        /// <summary>
        /// 根据顾客卡号获得顾客信息
        /// </summary>
        /// <param name="cardNo"></param>
        /// <returns></returns>
         public DataRow GetCustomerByCard(string cardNo)
         {
             if (string.IsNullOrEmpty(cardNo))
                 return null;

             var strSql = @"SELECT A.CUSTOMER_KEY,A.CARD_NO,A.NAME,A.MOBILE,B.NAME AS LEVEL_NAME,GIVE_POINTS AS POINTS
             FROM dbo.CUSTOMER A
             LEFT JOIN dbo.DIC_LEVEL B ON A.LEVEL_CODE=B.LEVEL_CODE
             INNER JOIN dbo.SYS_USER_SHOP  C ON A.SHOP_CODE=C.SHOP_CODE
             WHERE A.CARD_NO=@cardNo";
             var dbClass = DB.HR;
             var cmd = dbClass.GetSqlStringCommand(strSql);
             dbClass.AddInParameter(cmd, "cardNo", DbType.String, cardNo);
             var dt = dbClass.ExecuteDataSet(cmd).Tables[0];
             return (dt != null && dt.Rows.Count > 0) ? dt.Rows[0] : null;
         }

        /// <summary>
        /// 根据顾客卡号得到顾客姓名 卡号 手机号
        /// </summary>
        /// <param name="customerKey"></param>
        /// <returns></returns>
         public static DataRow GetCustomerByKey(string customerKey)
         {
             if (string.IsNullOrEmpty(customerKey))
                 return null;

             var strSql = @"SELECT CUSTOMER_KEY,CARD_NO,NAME,MOBILE
             FROM dbo.CUSTOMER A
             WHERE CUSTOMER_KEY=@CUSTOMER_KEY";
             var dbClass = DB.HR;
             var cmd = dbClass.GetSqlStringCommand(strSql);
             dbClass.AddInParameter(cmd, "CUSTOMER_KEY", DbType.String, customerKey);
             var dt = dbClass.ExecuteDataSet(cmd).Tables[0];
             return (dt != null && dt.Rows.Count > 0) ? dt.Rows[0] : null;
         }

         /// <summary>
         /// 生成兑换记录单号
         /// </summary>
         /// <returns></returns>
         public string GetExchangeNo()
         {
             var strSql = string.Format("SELECT DBO.fn_generate_exchange_no('{0}')", bllSYS_USER.CurrentUser.SHOP_CODE);
             var dbClass = DB.HR;
             var cmd = dbClass.GetSqlStringCommand(strSql);
             object exchangeNo=dbClass.ExecuteScalar(cmd);
             return exchangeNo.ToString();
         }

         /// <summary>
         /// 根据兑换主记录key取得兑换主记录
         /// </summary>
         /// <param name="exchangeKey"></param>
         /// <returns></returns>
         public DataRow GetExchangeByKey(string exchangeKey)
         {
             var strSql = @"SELECT [EXCHANGE_KEY]
              ,A.[EXCHANGE_NO]
              ,A.[CUSTOMER_KEY]
              ,A.[GIFTS]
              ,A.[DEDUCT_POINT]
              ,A.[DATE_EXCHANGE]
              ,A.[MEMO]
              ,C.ACCOUNT
              ,B.CARD_NO
              ,B.NAME
              ,B.MOBILE
              ,C.ACCOUNT
              ,D.NAME AS LEVEL_NAME
              ,B.GIVE_POINTS as POINTS
              ,d.POINT_DISCOUNT
              FROM [POINT_EXCHANGE] A
              INNER JOIN dbo.CUSTOMER B ON A.[CUSTOMER_KEY]=B.[CUSTOMER_KEY]
              INNER JOIN dbo.SYS_USER C ON A.[USER_CREATE]=C.USER_KEY
              LEFT JOIN dbo.DIC_LEVEL d ON b.LEVEL_CODE=d.LEVEL_CODE
              WHERE EXCHANGE_KEY=@EXCHANGE_KEY";
             var dbClass = DB.HR;
             var cmd = dbClass.GetSqlStringCommand(strSql);
             dbClass.AddInParameter(cmd, "EXCHANGE_KEY", DbType.Guid, exchangeKey.ToGuid());
             var dt = dbClass.ExecuteDataSet(cmd).Tables[0];
             return (dt != null && dt.Rows.Count > 0) ? dt.Rows[0] : null;
         }

         /// <summary>
         /// 取得兑换明细
         /// </summary>
         /// <param name="exchangeKey"></param>
         /// <returns></returns>
         public DataSet GetExchangeItem(string exchangeKey)
         {
             var strSql = @"SELECT B.NAME
              ,A.[GIFT_KEY]
              ,A.[NUM]
              ,A.[DEDUCT_POINT]
              FROM [POINT_EXCHANGE_ITEM] A
              INNER JOIN dbo.GIFTS B ON A.[GIFT_KEY]=B.[GIFT_KEY]
              WHERE A.EXCHANGE_KEY=@EXCHANGE_KEY";
             var dbClass = DB.HR;
             var cmd = dbClass.GetSqlStringCommand(strSql);
             dbClass.AddInParameter(cmd, "EXCHANGE_KEY", DbType.Guid, exchangeKey.ToGuid());
             var ds = dbClass.ExecuteDataSet(cmd);
             return ds;
         }

        /// <summary>
        /// 保存兑换主记录及明细
        /// </summary>
        /// <param name="EXCHANGE_KEY"></param>
        /// <param name="customerKey"></param>
        /// <param name="itemArr"></param>
        /// <returns></returns>
         public string SaveExchange(string EXCHANGE_KEY,string customerKey,ExchangeItemJson [] itemArr)
         { 
            var dalPointExchange=new dalPOINT_EXCHANGE();
            POINT_EXCHANGEinfo exchangeInfo=new POINT_EXCHANGEinfo();
            var cmdList = new List<DbCommand>();
            string pk = EXCHANGE_KEY;
            string exchangeNo =GetExchangeNo();
            string logContent = "";
            var gifts = "";
            int deduct_point = 0;
            foreach (var tmpItem in itemArr)//将兑换明细转化为文本，查询方便，格式礼品1（2），礼品2（3）。。。。
            {
                gifts += tmpItem.GIFT_NAME + "(" + tmpItem.NUM.ToString() + "),";
                deduct_point += Int16.Parse(tmpItem.DEDUCT_POINT);
            }
            if (gifts != "")
                gifts = gifts.Substring(0, gifts.Length - 1);

            DataRow tmpRow = bllPOINT_EXCHANGE.GetCustomerByKey(customerKey);

            if (!string.IsNullOrEmpty(EXCHANGE_KEY))//更新兑换主记录
            {
                pk = EXCHANGE_KEY;
                var cr = dalPointExchange.GetModel(new Guid(EXCHANGE_KEY));

                exchangeInfo.EXCHANGE_KEY = cr.EXCHANGE_KEY;
                exchangeInfo.EXCHANGE_NO=cr.EXCHANGE_NO;
                exchangeInfo.CUSTOMER_KEY=cr.CUSTOMER_KEY;
                exchangeInfo.GIFTS = gifts;
                exchangeInfo.DEDUCT_POINT = deduct_point;
                exchangeInfo.DATE_EXCHANGE=cr.DATE_EXCHANGE;
                exchangeInfo.STATUS=cr.STATUS;
                exchangeInfo.MEMO=cr.MEMO;
                exchangeInfo.USER_CREATE=cr.USER_CREATE;
                exchangeInfo.DATE_CREATE=cr.DATE_CREATE;
                exchangeInfo.DATE_UPDATE=DateTime.Now;
                cmdList.Add(dalPointExchange.GetUpdateDbCommand(exchangeInfo));

                if (tmpRow != null)
                {
                    logContent = string.Format("修改兑换记录，顾客姓名\"{0}\"  手机号\"{1}\":", tmpRow["NAME"].ToString(), tmpRow["MOBILE"].ToString());
                    if (cr.DEDUCT_POINT != exchangeInfo.DEDUCT_POINT)
                    {
                        logContent += string.Format("消耗积分由{0}改为{1};", cr.DEDUCT_POINT, exchangeInfo.DEDUCT_POINT);
                    }

                    if (cr.GIFTS != exchangeInfo.GIFTS)
                    {
                        logContent += string.Format("原兑换礼品:{0}，现兑换礼品：{1}", cr.GIFTS, exchangeInfo.GIFTS);
                    }

                    bllSYS_LOG.SaveLog("004001001", "POINT_EXCHANGE", cr.EXCHANGE_KEY.ToString(), logContent, "修改", BusinessBase.ClientIp);
                }

            }
            else//添加兑换明细
            {
                pk = DB.GetNewGuid(DB.HR);
                exchangeInfo.EXCHANGE_KEY =new Guid(pk) ;
                exchangeInfo.EXCHANGE_NO = exchangeNo;
                exchangeInfo.CUSTOMER_KEY =new Guid(customerKey);
                exchangeInfo.GIFTS = gifts;
                exchangeInfo.DEDUCT_POINT = deduct_point;
                exchangeInfo.DATE_EXCHANGE =DateTime.Now;
                exchangeInfo.STATUS =1;
                exchangeInfo.MEMO ="";
                exchangeInfo.USER_CREATE = bllSYS_USER.CurrentUser.USER_KEY;
                exchangeInfo.DATE_CREATE =DateTime.Now;
                exchangeInfo.DATE_UPDATE = DateTime.Now;
                cmdList.Add(dalPointExchange.GetAddDbCommand(exchangeInfo));
                if (tmpRow != null)
                {
                    logContent = string.Format("添加兑换记录，顾客姓名\"{0}\"  手机号\"{1}\":", tmpRow["NAME"].ToString(), tmpRow["MOBILE"].ToString());
                    bllSYS_LOG.SaveLog("004001001", "POINT_EXCHANGE",pk, logContent, "添加", BusinessBase.ClientIp);
                }
            }

            var dbClass = DB.HR;
            

            //修改兑换记录前先还原积分，都还原成赠送积分
            if (!string.IsNullOrEmpty(EXCHANGE_KEY))
            {
                var strSql = "SELECT DEDUCT_POINT FROM  POINT_EXCHANGE WHERE EXCHANGE_KEY=@EXCHANGE_KEY";

                var cmd = dbClass.GetSqlStringCommand(strSql);
                dbClass.AddInParameter(cmd, "EXCHANGE_KEY", DbType.Guid, new Guid(EXCHANGE_KEY));
                object old_DEDUCT_POINT = dbClass.ExecuteScalar(cmd);


                string sql_recover_point = "UPDATE CUSTOMER  "
                + "SET GIVE_POINTS=GIVE_POINTS+" +old_DEDUCT_POINT.ToString() + " WHERE CUSTOMER_KEY=@CUSTOMER_KEY";
                var recover_point_cmd = dbClass.GetSqlStringCommand(sql_recover_point);
                dbClass.AddInParameter(recover_point_cmd, "CUSTOMER_KEY", DbType.Guid, new Guid(customerKey));
                cmdList.Add(recover_point_cmd);
            }

            //先删除兑换明细
            const string sql_POINT_EXCHANGE_ITEM_delete = "DELETE FROM POINT_EXCHANGE_ITEM WHERE EXCHANGE_KEY=@EXCHANGE_KEY";
            var delPOINT_EXCHANGE_ITEMCmd = dbClass.GetSqlStringCommand(sql_POINT_EXCHANGE_ITEM_delete);
            dbClass.AddInParameter(delPOINT_EXCHANGE_ITEMCmd, "EXCHANGE_KEY", DbType.Guid,new Guid(pk));
            cmdList.Add(delPOINT_EXCHANGE_ITEMCmd);

            //减去积分
            const string sql_reduce_point = "UPDATE CUSTOMER  SET GIVE_POINTS=GIVE_POINTS-@DEDUCT_POINT"
                + " WHERE CUSTOMER_KEY=@CUSTOMER_KEY";
            var reduce_point_cmd = dbClass.GetSqlStringCommand(sql_reduce_point);
            dbClass.AddInParameter(reduce_point_cmd, "CUSTOMER_KEY", DbType.Guid, new Guid(customerKey));
            dbClass.AddInParameter(reduce_point_cmd, "DEDUCT_POINT", DbType.Int32, deduct_point);
            cmdList.Add(reduce_point_cmd);

            const string sql_POINT_EXCHANGE_ITEM_insert = "INSERT INTO POINT_EXCHANGE_ITEM(EXCHANGE_KEY,GIFT_KEY,NUM,DEDUCT_POINT,USER_CREATE)VALUES(@EXCHANGE_KEY,@GIFT_KEY,@NUM,@DEDUCT_POINT,@USER_CREATE)";
            foreach (var tmpItem in itemArr)//添加兑换明细
            {
                var cmd = dbClass.GetSqlStringCommand(sql_POINT_EXCHANGE_ITEM_insert);
                dbClass.AddInParameter(cmd, "EXCHANGE_KEY", DbType.Guid, new Guid(pk));
                dbClass.AddInParameter(cmd, "GIFT_KEY", DbType.Guid, new Guid(tmpItem.GIFT_KEY));
                dbClass.AddInParameter(cmd, "NUM", DbType.Int16, tmpItem.NUM);
                dbClass.AddInParameter(cmd, "DEDUCT_POINT", DbType.Int16, tmpItem.DEDUCT_POINT);
                dbClass.AddInParameter(cmd, "USER_CREATE", DbType.Guid, bllSYS_USER.CurrentUser.USER_KEY);
                cmdList.Add(cmd);
            }
            ExecTransaction.Exec(cmdList);

            return pk + "|" + exchangeNo;
         }

        /// <summary>
        /// 删除兑换记录，并非正真删除，打个标记，还原积分
        /// </summary>
        /// <param name="exchangeKey"></param>
        /// <returns></returns>
         public bool DeleteExchange(string exchangeKey)
         {
             var dbClass = DB.HR;
             var cmdList = new List<DbCommand>();
            
             try
             {
                 var strSql = "SELECT CUSTOMER_KEY FROM  POINT_EXCHANGE WHERE EXCHANGE_KEY=@EXCHANGE_KEY";

                 var cmd = dbClass.GetSqlStringCommand(strSql);
                 dbClass.AddInParameter(cmd, "EXCHANGE_KEY", DbType.Guid, new Guid(exchangeKey));
                 object customerKey = dbClass.ExecuteScalar(cmd);

                 string logContent = "";
                 DataRow tmpRow = bllPOINT_EXCHANGE.GetCustomerByKey(customerKey.ToString());
                 if (tmpRow != null)
                 {
                     logContent = string.Format("删除兑换记录，顾客姓名\"{0}\"  手机号\"{1}\":", tmpRow["NAME"].ToString(), tmpRow["MOBILE"].ToString());
                     bllSYS_LOG.SaveLog("004001001", "POINT_EXCHANGE", exchangeKey, logContent, "删除", BusinessBase.ClientIp);
                 }

                 //先删除兑换明细
                 const string sql_POINT_EXCHANGE_update = "UPDATE  POINT_EXCHANGE SET STATUS=9,DATE_UPDATE=GETDATE() WHERE EXCHANGE_KEY=@EXCHANGE_KEY";
                 var POINT_EXCHANGE_update_Cmd = dbClass.GetSqlStringCommand(sql_POINT_EXCHANGE_update);
                 dbClass.AddInParameter(POINT_EXCHANGE_update_Cmd, "EXCHANGE_KEY", DbType.Guid, new Guid(exchangeKey));
                 cmdList.Add(POINT_EXCHANGE_update_Cmd);

                 string sql_recover_point = "UPDATE CUSTOMER  "
                 + "SET GIVE_POINTS=GIVE_POINTS+isnull((SELECT DEDUCT_POINT "
                 + "FROM dbo.POINT_EXCHANGE "
                 + "WHERE EXCHANGE_KEY=@EXCHANGE_KEY),0) "
                 + " WHERE CUSTOMER_KEY=@CUSTOMER_KEY";
                 var recover_point_cmd = dbClass.GetSqlStringCommand(sql_recover_point);
                 dbClass.AddInParameter(recover_point_cmd, "CUSTOMER_KEY", DbType.Guid, new Guid(customerKey.ToString()));
                 dbClass.AddInParameter(recover_point_cmd, "EXCHANGE_KEY", DbType.Guid, new Guid(exchangeKey));
                 cmdList.Add(recover_point_cmd);
                 ExecTransaction.Exec(cmdList);
                 return true;
             }
             catch
             {
                 return false;
             }
            
         }

         public DataSet GetPersonExchange(string customerKey)
         {
             StringBuilder strSql = new StringBuilder();
             strSql.Append("select [EXCHANGE_KEY],[EXCHANGE_NO],[CUSTOMER_KEY],[GIFTS],[DEDUCT_POINT],convert(char(10),[DATE_EXCHANGE],120) DATE_EXCHANGE,[STATUS],[MEMO],convert(char(10),[DATE_CREATE],120) DATE_CREATE,[CARD_NO],[NAME],[MOBILE],[ACCOUNT],[REAL_NAME] from V_POINT_EXCHANGE ");
             strSql.Append(" where 1=1 and STATUS=1 and CUSTOMER_KEY=@CUSTOMER_KEY ");
             var dbClass = DB.HR;
             DbCommand dbCommand = dbClass.GetSqlStringCommand(strSql.ToString());
             dbClass.AddInParameter(dbCommand, "CUSTOMER_KEY", DbType.Guid,new Guid(customerKey));

             return dbClass.ExecuteDataSet(dbCommand);
         }

         public Suggests SuggestCustomer(string keyWord, int page)
         {
             var oSuggests = new Suggests();
             var currpage = page + 1;
             var pagesize = JKF.Substructure.Utils.GetPageSize;
             var sqlWhere =string.Format(" exists(select shop_code from sys_user_shop  where VL_CUSTOMER_VIEW.shop_code=sys_user_shop.shop_code  and sys_user_shop.USER_KEY='{0}')",bllSYS_USER.CurrentUser.USER_KEY.ToString());
             sqlWhere = sqlWhere+string.Format("  and  (CARD_NO like '%{0}%'  or NAME  like '%{0}%' or MOBILE  like '%{0}%')", keyWord.ToUpper());
    

             var rowCount = 0;
             var pageCount = 0;
             var ds = DataPage.GetPageInfo("VL_CUSTOMER_VIEW", "CUSTOMER_KEY", currpage, pagesize, "CUSTOMER_KEY,CARD_NO,NAME,SEX=case sex when '1' then '男' when '2' then '女' else '' end,MOBILE, level_name,AVAIPOINTS,POINT_DISCOUNT", "NAME", sqlWhere, ref pageCount, ref rowCount);

             oSuggests._pageCnt = pageCount;
             oSuggests.Items = new List<string>();
             oSuggests.Items_value = new List<string>();
             oSuggests.Data = ds;

             foreach (DataRow item in ds.Tables[0].Rows)
             {
                 oSuggests.Items_value.Add(item["CUSTOMER_KEY"].ToString());
                 oSuggests.Items.Add(string.Format("{0} {1} {2} {3} {4} {5} {6}", item["NAME"], item["SEX"], item["CARD_NO"], item["MOBILE"], item["level_name"], item["AVAIPOINTS"], item["POINT_DISCOUNT"]));
             }
             return oSuggests;
         }

    }
}
